package com.lagou.app;

import com.lagou.entity.Account;
import com.lagou.entity.Phone;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class TestClass {
    /*按照步骤 实现卡号：1122334455向55443332211转账5000元的操作；

    步骤

    a) 使用连接池创建QueryRunner对象；
    b) 判断转出方是否有足够余额，如果不足，提示信息：”余额不足！”，并结束程序；
    c) 通过卡号 进行转账的操作；
    d) 转账结束后, 将转入、转出记录分别写入到Transaction表中。*/
    @Test
    public void testAccount() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from account where card = ?";
        Account account = qr.query(sql, new BeanHandler<Account>(Account.class), "1122334455");
        //判断转账方的余额是否大于5000
        if (account.getBalance()>5000.0){
            System.out.println("可以转账");
        }else {
            System.out.println("余额不足！");
        }
    }
    //进行转账操作！
    @Test
    public void transferAccount() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "update account set balance = balance - ? where card = ?";
        Object[] para1 = {5000.0,"1122334455"};
        qr.update(sql,para1);
        String sql2 = "update account set balance = balance + ? where card = ?";
        Object[] para2 = {5000.0,"55443332211"};
        qr.update(sql2,para2);
    }
    //将转入转出的数据记录到Transaction表中
    @Test
    public void testTransaction() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "insert into transaction values(?,?,?,?,?)";
        Object[] para1 = {null,"1122334455","转出",5000.0, DateUtils.getTime()};
        qr.update(sql,para1);
        String sql2 = "insert into transaction values(?,?,?,?,?)";
        Object[] para2 = {null,"55443332211","转入",5000.0, DateUtils.getTime()};
        qr.update(sql2,para2);
    }
    /*需求1:  查询价格高于2000元，生产日期是2019年之前的所有手机*/
    @Test
    public void testPhone() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from phone where prodate < ? and price > ?";
        List<Phone> phoneList = qr.query(sql, new BeanListHandler<Phone>(Phone.class), "2019-01-01", 2000.0);
        for (Phone phone : phoneList) {
            System.out.println(phone);
        }
    }
     /*需求2:  查询所有颜色是白色的手机信息*/
    @Test
    public void testPhone2() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from phone where color = ?";
        List<Phone> phoneList = qr.query(sql, new BeanListHandler<Phone>(Phone.class),"白色");
        for (Phone phone : phoneList) {
            System.out.println(phone);
        }
    }


}
